package com.lulalei.dbviewer.db.mysql.dao;

import com.lulalei.dbviewer.commons.bean.JDBCConnectBean;
import com.lulalei.dbviewer.commons.bean.RowBean;
import com.lulalei.dbviewer.commons.bean.TableBean;
import com.lulalei.dbviewer.commons.cache.CacheManager;
import com.lulalei.dbviewer.commons.cache.CacheMap;
import com.lulalei.dbviewer.commons.exception.CustomizeException;
import com.lulalei.dbviewer.commons.utils.JDBCResultHandlerUtils;
import com.lulalei.dbviewer.db.DBDao;
import com.lulalei.dbviewer.db.mysql.DTO.ColumnsQueryDTO;
import com.lulalei.dbviewer.dto.DBTablesInfoDTO;
import org.springframework.stereotype.Component;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

@Component
public class MySqlDao implements DBDao {

    @Override
    public List<TableBean> getTables(DBTablesInfoDTO dbTablesInfoDTO) throws CustomizeException, SQLException {
        //获取缓存中的数据库连接对象
        CacheMap cacheMap = CacheManager.getCacheMap("jdbc");
        if (cacheMap == null) {
            throw new CustomizeException("请先连接数据库");
        }
        JDBCConnectBean connection = (JDBCConnectBean) cacheMap.get(dbTablesInfoDTO.getConnectionName());
        if (connection == null) {
            throw new CustomizeException("不存在的数据库连接");
        }

        //查询表信息
        String sql = "select TABLE_NAME tableName,TABLE_COMMENT tableComment from information_schema.tables where table_schema='" + connection.getDbInfo().getDbName() + "'";
        // 是否根据表名查询
        if (dbTablesInfoDTO.getTableNames() != null && !dbTablesInfoDTO.getTableNames().isEmpty()) {
            List<String> tableNames = dbTablesInfoDTO.getTableNames();
            sql = sql + " and TABLE_NAME in ('" + String.join("','", tableNames.toArray(new String[0])) + "')";
        }
        // 是否分页
        if (dbTablesInfoDTO.getPage() != null && dbTablesInfoDTO.getPageSize() != null) {
            if (dbTablesInfoDTO.getPage() != 0 && dbTablesInfoDTO.getPageSize() != 0) {
                sql = sql + " limit " + (dbTablesInfoDTO.getPageSize() * (dbTablesInfoDTO.getPage() - 1)) + "," + dbTablesInfoDTO.getPageSize();
            }
        }
        Statement statement = connection.getConnection().createStatement();
        ResultSet resultSet = statement.executeQuery(sql);

        return JDBCResultHandlerUtils.handleTables(resultSet);
    }

    @Override
    public Integer getTableTotal(DBTablesInfoDTO dbTablesInfoDTO) throws CustomizeException, SQLException {
        //获取缓存中的数据库连接对象
        CacheMap cacheMap = CacheManager.getCacheMap("jdbc");
        if (cacheMap == null) {
            throw new CustomizeException("请先连接数据库");
        }
        JDBCConnectBean connection = (JDBCConnectBean) cacheMap.get(dbTablesInfoDTO.getConnectionName());
        if (connection == null) {
            throw new CustomizeException("不存在的数据库连接");
        }

        String sql = "select count(*) total from information_schema.tables where table_schema='" + connection.getDbInfo().getDbName() + "'";
        Statement statement = connection.getConnection().createStatement();
        ResultSet resultSet = statement.executeQuery(sql);
        return JDBCResultHandlerUtils.handleCount(resultSet);
    }


    @Override
    public List<RowBean> getTableInfo(ColumnsQueryDTO columnsQueryDTO) throws CustomizeException, SQLException {
        //获取缓存中的数据库连接对象
        CacheMap cacheMap = CacheManager.getCacheMap("jdbc");
        if (cacheMap == null) {
            throw new CustomizeException("请先连接数据库");
        }
        JDBCConnectBean connection = (JDBCConnectBean) cacheMap.get(columnsQueryDTO.getConnectionName());
        if (connection == null) {
            throw new CustomizeException("不存在的数据库连接");
        }

        String sql = "select COLUMN_NAME columnName,DATA_TYPE columnType,COLUMN_COMMENT columnComment,COLUMN_KEY columnKey from information_schema.columns where table_schema ='" + connection.getDbInfo().getDbName() + "'  and table_name = '" + columnsQueryDTO.getTableName() + "' order by ORDINAL_POSITION;";

        Statement statement = connection.getConnection().createStatement();
        ResultSet resultSet = statement.executeQuery(sql);
        return JDBCResultHandlerUtils.handleColumns(resultSet);
    }

}
